-- CREATE FOREIGN KEY dat cho -khach hang (1)
IF EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE NAME='FK_DATCHO_KHACHHANG')
BEGIN
	ALTER TABLE DATCHO
	DROP CONSTRAINT FK_DATCHO_KHACHHANG
END
GO
ALTER TABLE DATCHO
ADD CONSTRAINT FK_DATCHO_KHACHHANG
FOREIGN KEY (MAKH)
REFERENCES KHACHHANG(MAKH)

--CREATE FOREIGN KEY PHANCONG_MA_NVIEN - NHANVIEN_MANV (2) 
IF EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE NAME='FK_DATCHO_LICHBAY')
BEGIN
	ALTER TABLE DATCHO
	DROP CONSTRAINT FK_DATCHO_LICHBAY
END
GO
ALTER TABLE DATCHO
ADD CONSTRAINT FK_DATCHO_LICHBAY
FOREIGN KEY (MACB,NGAYDI)
REFERENCES LICHBAY(MACB,NGAYDI)

-- CREATE FOREIGN KEY lichbay_chuyenbay (3)
IF EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE NAME='FK_LICHBAY_CHUYENBAY')
BEGIN
	ALTER TABLE LICHBAY
	DROP CONSTRAINT FK_LICHBAY_CHUYENBAY
END
GO
ALTER TABLE LICHBAY
ADD CONSTRAINT FK_LICHBAY_CHUYENBAY
FOREIGN KEY (MACB)
REFERENCES CHUYENBAY(MACB)

-- CREATE FOREIGN KEY lichbay_maybay (4)
IF EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE NAME='FK_LICHBAY_MAYBAY')
BEGIN
	ALTER TABLE LICHBAY
	DROP CONSTRAINT FK_LICHBAY_MAYBAY
END
GO
ALTER TABLE LICHBAY
ADD CONSTRAINT FK_LICHBAY_MAYBAY
FOREIGN KEY (SOHIEU,MALOAI)
REFERENCES MAYBAY(SOHIEU,MALOAI)

-- CREATE FOREIGN KEY may bay_loai may bay (5)
IF EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE NAME='FK_MAYBAY_LOAIMB')
BEGIN
	ALTER TABLE MAYBAY
	DROP CONSTRAINT FK_MAYBAY_LOAIMB
END
GO
ALTER TABLE MAYBAY
ADD CONSTRAINT FK_MAYBAY_LOAIMB
FOREIGN KEY	(MALOAI)
REFERENCES LOAIMB(MALOAI)

-- CREATE FOREIGN KEY KHANANG_LOAIMB (6)
IF EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE NAME='FK_KHANANG_LOAIMB')
BEGIN
	ALTER TABLE KHANANG
	DROP CONSTRAINT FK_KHANANG_LOAIMB
END
GO
ALTER TABLE KHANANG
ADD CONSTRAINT FK_KHANANG_LOAIMB
FOREIGN KEY (MALOAI)
REFERENCES LOAIMB(MALOAI)
--create foreign key KHANANG_NHANVIEN
if exists (select * from sys.foreign_keys where name='FK_KHANANG_NHANVIEN')
begin
	alter table KHANANG drop constraint FK_KHANANG_NHANVIEN
end
go

alter table KHANANG
add constraint FK_KHANANG_NHANVIEN
foreign key (MANV)
references NHANVIEN(MANV)
--create foreign key PHANCONG_NHANVIEN
if exists (select * from sys.foreign_keys where name='FK_PHANCONG_NHANVIEN')
begin
	alter table PHANCONG drop constraint FK_PHANCONG_NHANVIEN
end
go

alter table PHANCONG
add constraint FK_PHANCONG_NHANVIEN
foreign key (MANV)
references NHANVIEN(MANV)
--create foreign key PHANCONG_LICHBAY
if exists (select * from sys.foreign_keys where name='FK_PHANCONG_LICHBAY')
begin
	alter table PHANCONG drop constraint FK_PHANCONG_LICHBAY
end
go

alter table PHANCONG
add constraint FK_PHANCONG_LICHBAY
foreign key (NGAYDI,MACB)
references LICHBAY(NGAYDI,MACB)


